﻿//============================================================
//author:zhxitao
//============================================================

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Saker.Kernel.Model;

namespace Saker.Kernel.DAL
{
    #region SysDepartment数据访问层
	/// <summary>
	///SysDepartment数据访问层
	/// </summary>	
	public partial class SysDepartmentDAL
	{
        #region SysDepartment数据访问层 自动生成
		/// <summary>
        /// 添加SysDepartment
        /// </summary>
        /// <param name="SysDepartment">SysDepartment</param>
        /// <returns>添加后的SysDepartment</returns>
        public SysDepartment Add
			(SysDepartment sysDepartment)
		{
				string sql ="INSERT INTO SysDepartment (DepartName, ParentID, ShowOrder, DLevel, ChildCount)  VALUES (@DepartName, @ParentID, @ShowOrder, @DLevel, @ChildCount) select @@identity";
				SqlParameter[] para = new SqlParameter[]
					{
						new SqlParameter("@DepartName", ToDBValue(sysDepartment.DepartName)),
						new SqlParameter("@ParentID", ToDBValue(sysDepartment.ParentID)),
						new SqlParameter("@ShowOrder", ToDBValue(sysDepartment.ShowOrder)),
						new SqlParameter("@DLevel", ToDBValue(sysDepartment.DLevel)),
						new SqlParameter("@ChildCount", ToDBValue(sysDepartment.ChildCount)),
					};
					
				int newId = Convert.ToInt32(SqlHelper.ExecuteScalar(sql, para));
				return GetByDepartID(newId);
		}

		/// <summary>
        /// 根据主键删除SysDepartment
        /// </summary>
        /// <param name="DepartID">DepartID</param>
        /// <returns>影响的行数</returns>
        public int DeleteByDepartID(int departID)
		{
            string sql = "DELETE SysDepartment WHERE DepartID = @DepartID";

           SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@DepartID", departID)
			};
		
            return SqlHelper.ExecuteNonQuery(sql, para);
		}
		
				
		/// <summary>
		/// 更新SysDepartment
		/// </summary>
		/// <param name="SysDepartment">SysDepartment</param>
		/// <returns></returns>
        public int Update(SysDepartment sysDepartment)
        {
            string sql =
                "UPDATE SysDepartment " +
                "SET " +
			" DepartName = @DepartName" 
                +", ParentID = @ParentID" 
                +", ShowOrder = @ShowOrder" 
                +", DLevel = @DLevel" 
                +", ChildCount = @ChildCount" 
               
            +" WHERE DepartID = @DepartID";


			SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@DepartID", sysDepartment.DepartID)
					,new SqlParameter("@DepartName", ToDBValue(sysDepartment.DepartName))
					,new SqlParameter("@ParentID", ToDBValue(sysDepartment.ParentID))
					,new SqlParameter("@ShowOrder", ToDBValue(sysDepartment.ShowOrder))
					,new SqlParameter("@DLevel", ToDBValue(sysDepartment.DLevel))
					,new SqlParameter("@ChildCount", ToDBValue(sysDepartment.ChildCount))
			};

			return SqlHelper.ExecuteNonQuery(sql, para);
        }		
		
		/// <summary>
		/// 根据主键查询SysDepartment实体
		/// </summary>
		/// <param name="SysDepartment">SysDepartment</param>
		/// <returns>SysDepartment</returns>
        public SysDepartment GetByDepartID(int departID)
        {
            string sql = "SELECT * FROM SysDepartment WHERE DepartID = @DepartID";
            using(SqlDataReader reader = SqlHelper.ExecuteDataReader(sql, new SqlParameter("@DepartID", departID)))
			{
				if (reader.Read())
				{
					return ToModel(reader);
				}
				else
				{
					return null;
				}
       		}
        }
		/// <summary>
		/// SqlDataReader对象转换为SysDepartment实体
		/// </summary>
		/// <param name="reader">SqlDataReader对象</param>
		/// <returns>SysDepartment实体</returns>
		public SysDepartment ToModel(SqlDataReader reader)
		{
			SysDepartment sysDepartment = new SysDepartment();

			sysDepartment.DepartID = (int)ToModelValue(reader,"DepartID");
			sysDepartment.DepartName = (string)ToModelValue(reader,"DepartName");
			sysDepartment.ParentID = (int)ToModelValue(reader,"ParentID");
			sysDepartment.ShowOrder = (int)ToModelValue(reader,"ShowOrder");
			sysDepartment.DLevel = (int?)ToModelValue(reader,"DLevel");
			sysDepartment.ChildCount = (int?)ToModelValue(reader,"ChildCount");
			return sysDepartment;
		}
		/// <summary>
		/// 得到数据总行数
		/// </summary>
		/// <returns></returns>
		public int GetTotalCount()
		{
			string sql = "SELECT count(1) FROM SysDepartment";
			return (int)SqlHelper.ExecuteScalar(sql);
		}
		/// <summary>
		/// 分页，此方法仅在sql 2005下使用
		/// </summary>
		/// <param name="minrownum">起始行数</param>
		/// <param name="maxrownum">>结束行数</param>
		/// <returns>SysDepartment集合</returns>
		public IEnumerable<SysDepartment> GetPagedData(int minrownum,int maxrownum)
		{
			string sql = "SELECT * from(SELECT *,row_number() over(order by DepartID) rownum FROM SysDepartment) t where rownum>=@minrownum and rownum<=@maxrownum";
			using(SqlDataReader reader = SqlHelper.ExecuteDataReader(sql,
				new SqlParameter("@minrownum",minrownum),
				new SqlParameter("@maxrownum",maxrownum)))
			{
				return ToModels(reader);					
			}
		}
        /// <summary>
        /// 分页，调用存储过程
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="recordCount">记录数</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="pageCount">总页数</param>
        /// <param name="whereCondition">where</param>
        /// <returns>DataTable数据集</returns>
        public DataTable GetPagingRecord(int pageIndex, ref int recordCount, int pageSize, ref int pageCount,string whereCondition)
        {
            return SqlHelper.GetPagingRecord(pageIndex,ref recordCount,pageSize,ref pageCount,"SysDepartment","DepartID",whereCondition,"*","DepartID",true);
        }
		/// <summary>
		/// 得到所有SysDepartment集合
		/// </summary>
		/// <returns>所有集合</returns>
		public IEnumerable<SysDepartment> GetAll()
		{
			string sql = "SELECT * FROM SysDepartment";
			using(SqlDataReader reader = SqlHelper.ExecuteDataReader(sql))
			{
				return ToModels(reader);			
			}
		}
		/// <summary>
		/// SqlDataReader对象转换到SysDepartment集合
		/// </summary>
		/// <param name="reader">SqlDataReader对象</param>
		/// <returns>SysDepartment集合</returns>
		protected IEnumerable<SysDepartment> ToModels(SqlDataReader reader)
		{
			var list = new List<SysDepartment>();
			while(reader.Read())
			{
				list.Add(ToModel(reader));
			}	
			return list;
		}

        /// <summary>
        /// 根据上级部门ID得到所有SysDepartment集合
        /// </summary>
        /// <returns>所有集合</returns>
        public IEnumerable<SysDepartment> GetAllByParentID(int parentid)
        {
            string sql = "SELECT * FROM SysDepartment WHERE ParentID=@ParentID";
            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ParentID", parentid)
			};
            using (SqlDataReader reader = SqlHelper.ExecuteDataReader(sql,para))
            {
                return ToModels(reader);
            }
        }
		/// <summary>
		/// 转换数据值
		/// </summary>
		/// <param name="value">object类型</param>
		/// <returns>object对象</returns>
		protected object ToDBValue(object value)
		{
			if(value==null)
			{
				return DBNull.Value;
			}
			else
			{
				return value;
			}
		}
		/// <summary>
		/// 转换实体值
		/// </summary>
		/// <param name="reader">SqlDataReader对象</param>
		/// <param name="columnName">列名称</param>
		/// <returns>值</returns>
		protected object ToModelValue(SqlDataReader reader,string columnName)
		{
			if(reader.IsDBNull(reader.GetOrdinal(columnName)))
			{
				return null;
			}
			else
			{
				return reader[columnName];
			}
		}
        #endregion


        public bool Add(int ParentID, string DepartName)
        {
            string sql = "SysDepartment_Add";
            SqlParameter[] para = new SqlParameter[]
					{
                        new SqlParameter("@ParentID", ParentID),
						new SqlParameter("@DepartName",DepartName)	
					};
            return SqlHelper.ExecuteProc(sql,para);
        }
	}
    #endregion
}
